﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using HomeCare.View;
using System.Data;

namespace HomeCare.Model
{
    public class AgendaDados
    {
        Conexao con = new Conexao();

        public bool InsertAgenda(Agenda agenda)
        {
            SqlConnection sql = con.Conectar();
            sql.Open();
            string query = String.Format("insert into Agenda (Pessoas_ID, Funcionario_ID, DtInicial, DtFinal) values ({0},{1},'{2}','{3}')", agenda.IdPaciente, agenda.IdFuncionario, agenda.DataInicial.ToString("MM/dd/yyyy HH:mm:00"), agenda.DataFinal.ToString("MM/dd/yyyy HH:mm:00"));
            try
            {
                SqlCommand cmd = new SqlCommand(query, sql);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                sql.Close();
                return true;
            }
            catch (Exception ex)
            {
                sql.Close();
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public DataSet PesquisaAgenda(Agenda agenda)
        {
            SqlConnection sql = con.Conectar();
            sql.Open();
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select Agenda.DtInicial, Agenda.DtFinal, Pessoas.Nome, Enderecos.Rua,");
            sb.AppendLine("Enderecos.Numero, Enderecos.Bairro, Cidades.Cidade, Agenda.Pessoas_ID, Agenda.Funcionario_ID, Agenda.ID");
            sb.AppendLine("from Agenda");
            sb.AppendLine("inner join Pessoas on Agenda.Pessoas_ID = Pessoas.ID");
            sb.AppendLine("inner join Enderecos on Pessoas.ID = Agenda.Pessoas_ID");
            sb.AppendLine("inner join Cidades on Enderecos.Cidades_ID = Cidades.ID");
            sb.AppendFormat(" where Funcionario_ID = {0} and DtInicial BETWEEN '{1}' AND '{2}'", agenda.IdFuncionario, agenda.DataInicial.ToString("MM/dd/yyyy HH:mm:00"), agenda.DataFinal.ToString("MM/dd/yyyy HH:mm:00"));
            sb.AppendLine(" order by Agenda.DtInicial");
            string query = sb.ToString();
            try
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(query, sql);
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                sql.Close();
                return dataSet;
            }
            catch (Exception ex)
            {
                sql.Close();
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        public bool ExisteAgenda(Agenda agenda)
        {
            SqlConnection sql = con.Conectar();
            sql.Open();
            string query = String.Format("SELECT * FROM Agenda where ('{0}' >= DtFinal and '{0}' <= DtFinal) and ('{1}' > DtInicial and '{1}' <= DtFinal) and Funcionario_ID = {2}", agenda.DataInicial.ToString("MM/dd/yyyy HH:mm:00"), agenda.DataFinal.ToString("MM/dd/yyyy HH:mm:00"), agenda.IdFuncionario);
            //string query = String.Format("select * from Agenda where Funcionario_ID = {0} and DtInicial BETWEEN '{1}' AND '{2}' AND DtFinal BETWEEN '{3}' and '{4}'", agenda.IdFuncionario, agenda.DataInicial.ToString("MM/dd/yyyy HH:mm:00"), agenda.DataFinal.ToString("MM/dd/yyyy HH:mm:00"));
            try
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(query, sql);
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                sql.Close();

                if (dataSet.Tables[0].Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                sql.Close();
                MessageBox.Show(ex.Message);
                return true;
            }
        }

        public bool AlteraAgenda(Agenda agenda)
        {
            SqlConnection sql = con.Conectar();
            sql.Open();
            string query = String.Format("update Agenda set DtInicial='{0}', DtFinal='{1}', Pessoas_ID ='{2}', Funcionario_ID='{3}' where id='{4}'", agenda.DataInicial, agenda.DataFinal, agenda.IdPaciente, agenda.IdFuncionario, agenda.Id);
            try
            {
                SqlCommand cmd = new SqlCommand(query, sql);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                sql.Close();
                return true;
            }
            catch (Exception ex)
            {
                //throw new Exception("Erro ao Alterar Medicamento.");
                sql.Close();
                MessageBox.Show(ex.Message);
                return false;
            }
        }


        public bool ExcluiAgenda(Agenda agenda)
        {
            SqlConnection sql = con.Conectar();
            sql.Open();
            string query = String.Format("delete from Agenda where Id='{0}'", agenda.Id);
            try
            {
                SqlCommand cmd = new SqlCommand(query, sql);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                sql.Close();
                return true;
            }
            catch (Exception ex)
            {
                //throw new Exception("Erro ao Alterar Medicamento.");
                sql.Close();
                MessageBox.Show(ex.Message);
                return false;
            }
        }
    }
}
